﻿using System;
using System.Collections.Generic;
using System.Text;

namespace DusonFramework.DataAccess.Paging
{
    public class Paing_TopTop : PagingAbstract
    {
        /*
        select [需要显示的字段] from [表名|视图名] where [主键字段] in 

            ( select top PageSize [主键字段] from 
                (select top {PageSize * PageIndex} [主键字段] , [排序字段] from     --有几个排序字段就写几个字段

                    [表名|视图名] 
                    [ where 查询条件 ] 
                    order by 
                    
                        [排序字段1] asc|desc ,
                        [排序字段2] desc|asc, 
                        
                        [主键字段] asc|desc     
                ) as aa     
                order by     
                    [排序字段1] desc|asc,        --如果上面是倒序，那么这里就是正序，下同
                        [排序字段2] asc|desc ,
                            
                            [主键字段] desc|asc 
            ) 

        order by 
            [排序字段1] asc|desc,    --如果上面是倒序，那么这里就是正序，所谓颠颠倒倒嘛。
            [排序字段2] desc|asc,
            
            [主键字段] asc|desc
         
         select * from Products where ProductID in 
                ( select top 10 ProductID from 
                    (select top 20 ProductID , UnitPrice from     
                        Products 
                        where CategoryID = 3
                        order by 
                            UnitPrice desc ,
                            ProductID      
                    ) as aa     
                    order by     
                        UnitPrice asc,        --如果上面是倒序，那么这里就是正序，下同
                            ProductID desc 
                ) 
         order by 
                UnitPrice desc,    --如果上面是倒序，那么这里就是正序。
                ProductID 
         */

        /// <summary>
        /// opTop颠倒法
        /// 适用于有一个或以上的排序字段
        /// 要求：有主键
        /// 建议：为排序的组合设置非聚集索引，否则很慢
        /// </summary>
        /// <param name="table"></param>
        /// <param name="uniqueKey"></param>
        /// <param name="fields"></param>
        /// <param name="filter"></param>
        /// <param name="sortField"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public override string GetPagingSQL(string table, string uniqueKey, string fields, string filter, string sortField, int pageIndex, int pageSize)
        {
            StringBuilder sql = new StringBuilder();
            string where = string.Empty;
            string sortFieldColl = string.Empty;
            string sortField1 = string.Empty;
            string sortField2 = string.Empty;

            sortFieldColl = sortField.ToLower().Replace("asc", "").Replace("desc", "").TrimEnd(',');
            SortFieldBuilder(uniqueKey, sortField, ref sortField1, ref sortField2);

            if(!string.IsNullOrEmpty(filter))
                where = "where " + filter;

            if (pageIndex != 100001)
            {
                sql.Append(string.Format("select {0} from {1} where {2} in", fields, table, uniqueKey));
                sql.Append(string.Format("    ( select top {0} {1} from", pageSize, uniqueKey));
                sql.Append(string.Format("        ( select top {0} {1}, {2}", pageIndex * pageSize, uniqueKey, sortFieldColl));
                sql.Append(string.Format("          from {0} {1}", table, where));
                sql.Append(string.Format("          order by {0}", sortField1));
                sql.Append(string.Format("        ) as tempTable "));
                sql.Append(string.Format("      order by {0}", sortField2));
                sql.Append(string.Format("    )"));
                sql.Append(string.Format("order by {0}", sortField1));
            }
            else // 修改了最后一页总是显示PageSize条记录的bug
            {
                //select * from (
                //select top 剩余的记录数 * from TableName order by FunctionID desc ) as t 
                //order by t.FunctionID
                sql.Append(string.Format(" select {0} from ( select top {1} {0} from", fields, 9)); //选出剩余的记录
                sql.Append(string.Format("                  {0} {1}", table, where));
                sql.Append(string.Format("                  order by {0}", sortField2));
                sql.Append(string.Format("                ) as tempTable"));
                sql.Append(string.Format(" order by {0}", sortField1));
            }

            return sql.ToString();
        }

        /// <summary>
        /// 构造颠排序字段和倒排序字段
        /// </summary>
        /// <param name="uniqueKey"></param>
        /// <param name="sortField"></param>
        /// <param name="OrderColA"></param>
        /// <param name="OrderColB"></param>
        private void SortFieldBuilder(string uniqueKey, string sortField, ref string OrderColA, ref string OrderColB)
        {
            //转换成小写形式，判断是正序还是倒序，然后按照,号拆分
            string[] ArrOrderCol = sortField.ToLower().Split(',');

            string tmpCol = "";

            foreach (string a in ArrOrderCol)
            {
                if (a.Contains("desc"))
                {
                    //倒序
                    tmpCol = a.Replace("desc", "").Trim();
                    OrderColA += tmpCol + " desc,";
                    OrderColB += tmpCol + ",";
                }
                else
                {
                    //正序
                    tmpCol = a.Replace("asc", "").Trim();
                    OrderColA += tmpCol + ",";
                    OrderColB += tmpCol + " desc,";
                }
            }

            if (ArrOrderCol[ArrOrderCol.Length - 1].Contains("desc"))
            {
                OrderColA += uniqueKey;
                OrderColB += uniqueKey + " desc";
            }
            else
            {
                OrderColA += uniqueKey + " desc";
                OrderColB += uniqueKey;
            }

            OrderColA = OrderColA.TrimEnd(',');
            OrderColB = OrderColB.TrimEnd(',');
        }

    }
}
